package com.example.tuShare.tuShareService.tushareServiceImpl;

import com.example.tuShare.ApiResponse;
import com.example.tuShare.DTO.FinaIndicatorProperties;
import com.example.tuShare.DTO.FinaIndicatorRequest;
import com.example.tuShare.DTO.TradeDay;
import com.example.tuShare.tuShareService.TushareService;
import com.example.tuShare.utils.DateUtils;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import io.micrometer.common.util.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.stream.Collectors;

import lombok.extern.slf4j.Slf4j;


@Service
@Slf4j
public class TuShareServiceImpl implements TushareService {

    // 日期格式化器
    private static final DateTimeFormatter BASIC_FORMATTER = DateTimeFormatter.BASIC_ISO_DATE;

    @Autowired
    private FinaIndicatorProperties finaIndicatorProperties;

    // Tushare API 地址
    private static final String API_URL = "http://api.tushare.pro";

    /**
     * 调用 Tushare 的 fina_indicator 接口
     *
     * @param finaIndicatorRequest TS股票代码，例如 "600000.SH"
     * @return 返回的 JSON 数据
     */
    public ApiResponse callFinaIndicator(FinaIndicatorRequest finaIndicatorRequest, String token) {
        try {
            // 获取字段列表
            String fields = String.join(",", finaIndicatorProperties.getFields());

            String requestBody = String.format(
                    "{\"api_name\": \"fina_indicator_vip\", \"token\": \"%s\", \"params\": {\"ts_code\": \"%s\", \"period\": \"%s\", \"ann_date\": \"%s\", \"start_date\": \"%s\", \"end_date\": \"%s\"}, \"fields\": \"%s\"}",
                    token, finaIndicatorRequest.getTsCode(), finaIndicatorRequest.getPeriod(),
                    finaIndicatorRequest.getAnnDate(), finaIndicatorRequest.getStartDate(),
                    finaIndicatorRequest.getEndDate(), fields
            );

            // 创建 HTTP 连接
            URL url = new URL(API_URL);
            HttpURLConnection connection = (HttpURLConnection) url.openConnection();
            connection.setRequestMethod("POST");
            connection.setRequestProperty("Content-Type", "application/json");
            connection.setDoOutput(true);

            // 发送请求
            connection.getOutputStream().write(requestBody.getBytes());

            // 读取响应
            BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
            StringBuilder response = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                response.append(line);
            }
            reader.close();

            // 导出 Excel 文件
            try {
                parseAndExportToExcel(response.toString(), finaIndicatorRequest.getEndDate() + "FinaIndicator.xlsx", "财务指标数据");
                return new ApiResponse(200, "文件生成成功,文件名为:" + finaIndicatorRequest.getEndDate() + "FinaIndicator.xlsx");
            } catch (Exception e) {
                return new ApiResponse(-1, "文件生成失败: " + e.getMessage());
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ApiResponse(-1, "调用 Tushare API 失败: " + e.getMessage());
        }
    }

    @Override
    public void callFinaIndicator(String token) {
        // 定义日期格式化器，格式为yyyyMMdd
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");

        // 定义季度末的月份和日期
        int[][] quarters = {
                {3, 31},  // Q1
                {6, 30},   // Q2
                {9, 30},   // Q3
                {12, 31}   // Q4
        };

        // 外层循环年份，内层循环季度
        for (int year = 2010; year <= 2024; year++) {
            for (int[] quarter : quarters) {
                int month = quarter[0];
                int day = quarter[1];
                LocalDate date = LocalDate.of(year, month, day);
                // 格式化为yyyyMMdd的字符串
                String dateStr = date.format(formatter);
                // 这里每次循环只生成并输出一个日期字符串
                FinaIndicatorRequest finaIndicatorRequest = new FinaIndicatorRequest();
                finaIndicatorRequest.setPeriod(dateStr);
                finaIndicatorRequest.setEndDate(dateStr);
                finaIndicatorRequest.setTsCode("");
                finaIndicatorRequest.setAnnDate("");
                finaIndicatorRequest.setStartDate("");
                callFinaIndicator(finaIndicatorRequest, token);
            }
        }
    }

    @Override
    public ApiResponse callDailyBasic(FinaIndicatorRequest finaIndicatorRequest, String token) {

        String fields = "ts_code,trade_date,close,turnover_rate,turnover_rate_f,volume_ratio,pe,pe_ttm,pb,ps,ps_ttm,dv_ratio,dv_ttm,total_share,float_share,free_share,total_mv,circ_mv";

        //创建HTTP链接
        try {
            String requestBody = String.format(
                    "{\"api_name\": \"daily_basic\", \"token\": \"%s\", \"params\": {\"ts_code\": \"%s\",  \"trade_date\": \"%s\", \"start_date\": \"%s\", \"end_date\": \"%s\"},\"fields\": \"%s\"}",
                    token, finaIndicatorRequest.getTsCode(), finaIndicatorRequest.getTradeDate(), finaIndicatorRequest.getStartDate(), finaIndicatorRequest.getEndDate(), fields
            );

            URL url = new URL(API_URL);
            HttpURLConnection urlConnection = (HttpURLConnection) url.openConnection();
            urlConnection.setRequestMethod("POST");
            urlConnection.setRequestProperty("Content-Type", "application/json");
            urlConnection.setDoOutput(true);

            urlConnection.getOutputStream().write(requestBody.getBytes());

            //读取响应
            BufferedReader reader = new BufferedReader(new InputStreamReader(urlConnection.getInputStream()));
            StringBuilder response = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                response.append(line);
            }
            reader.close();

            // 导出 Excel 文件
            try {
                parseAndExportToExcel(response.toString(), finaIndicatorRequest.getTradeDate() + "DailyBasic.xlsx", "每日指标数据");
                return new ApiResponse(200, "文件生成成功,文件名为:" + finaIndicatorRequest.getTradeDate() + "DailyBasic.xlsx");
            } catch (Exception e) {
                return new ApiResponse(-1, "文件生成失败: " + e.getMessage());
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ApiResponse(-1, "调用 Tushare API 失败: " + e.getMessage());
        }
    }

    @Override
    public void callDailyBasic(String token) {
        //获取tushare的交易日
        List<TradeDay> tradeDate = getTradeDate("20100101", "20250325", token);
        processSpecialThursdays(tradeDate, token);

    }

    @Override
    public ApiResponse getCsvAndOutput(FinaIndicatorRequest finaIndicatorRequest) {
        String startDate = null; // 起始日期
        String endDate = null; // 终止日期
        String outPutFilePath = finaIndicatorRequest.getOutputPath(); // 输出表文件路径
        // 修改(或获取)上市CSV文件名
        List<String> csvList = listCsvFiles(finaIndicatorRequest.getFolderPath());
        //修改(或获取)已退市CSV文件名
        List<String> deListingCsvList = listCsvFiles(finaIndicatorRequest.getDeListingFolderPath());

        //将已退市的CSV文件与上市CSV文件汇总
        try {
            for (String deListingFile : deListingCsvList) {
                deListingFile = deListingFile + ".csv";
                // 读取 CSV 文件
                List<String[]> data = readDeListingCSV(finaIndicatorRequest.getDeListingFolderPath() + deListingFile);

                // 按日期正序排序
                sortByDate(data);

                // 写入排序后的数据到新的 CSV 文件
                writeDeListingCSV(finaIndicatorRequest.getDeListingFolderPath() + deListingFile, data);

                System.out.println("CSV 文件已按日期正序排序并保存到: " + deListingFile);
            }
        } catch (Exception e) {
            return new ApiResponse(-1, "倒序排列退市CSV表数据失败:" + e.getMessage());
        }

        try (FileInputStream outPutFile = new FileInputStream(outPutFilePath);
             Workbook outPutBook = new XSSFWorkbook(outPutFile)) {

            // 创建一个新的Sheet，用于存储合并后的数据
            int sheets = outPutBook.getNumberOfSheets(); // 获取有几个sheet页,进行循环
            Sheet newSheet = outPutBook.createSheet("汇总页");
            Sheet sheet = null;

            for (int i = 0; i < sheets; i++) {
                // 当前sheet页股票集合
                ArrayList<String> stockCodeList = new ArrayList<>(); // 输出表第二行的股票代码
                Map<String, Double> csvDateList = new HashMap<>(); // 每个sheet页都要重新建立集合供以后判断是否为空
                sheet = outPutBook.getSheetAt(i);

                // 获取初始加权净值
                double initialNetValue = 0.0;
                if (null != sheet.getRow(0).getCell(2) && StringUtils.isNotEmpty(sheet.getRow(0).getCell(2).toString())) {
                    initialNetValue = Double.parseDouble(sheet.getRow(0).getCell(2).toString());
                } else {
                    log.error("第" + (i + 1) + "页没有初始净值");
                    return new ApiResponse(-1, "第" + (i + 1) + "页没有初始净值"); // 跳过当前sheet页
                }

                // 获取当前sheet页包含的股票代码
                for (int j = 3; j < sheet.getRow(2).getLastCellNum(); j++) {
                    stockCodeList.add(sheet.getRow(2).getCell(j).toString());
                }

                // 获取输出表，读取起始日期和终止日期
                DataFormatter dataFormatter = new DataFormatter();
                startDate = dataFormatter.formatCellValue(sheet.getRow(0).getCell(3)); // 第4列
                endDate = dataFormatter.formatCellValue(sheet.getRow(0).getCell(4)); // 第5列

                for (String stockCode : stockCodeList) {
                    int columnIndex = -1;
                    for (Cell cell : sheet.getRow(2)) {
                        if (stockCode.equals(cell.toString())) {
                            columnIndex = cell.getColumnIndex();
                            break;
                        }
                    }

                    if (columnIndex == -1) {
                        log.error("未找到股票代码 {} 对应的列", stockCode);
                        continue;
                    }

                    for (String csvName : csvList) { // 文件夹中有几个csv文件
                        csvName = csvName.replace(".csv", "");
                        if (stockCode.equals(csvName)) {
                            String csvFilePath = finaIndicatorRequest.getFolderPath() + csvName + ".csv"; // CSV 文件路径

                            // 读取 CSV 数据
                            csvDateList = readCsvData(csvFilePath);
                            if (csvDateList.isEmpty()) {
                                log.error("CSV 文件 {} 数据为空或格式错误", csvFilePath);
                                return new ApiResponse(-1, "CSV 文件 {} 数据为空或格式错误" + csvFilePath);
                            }

                            // 获取起始日的上一个可用交易日的收盘价（a）
                            Double startDateClosePrice = getStartDateClosePrice(csvDateList, startDate);
                            if (startDateClosePrice == null) {
                                log.error("未找到起始日或上一个可用交易日的收盘价");
                                return new ApiResponse(-1, "未找到起始日或上一个可用交易日的收盘价");
                            }

                            // 计算起始日到结束日每日的涨跌幅，并写入输出表
                            ApiResponse apiResponse = calculateAndWritePriceChange(outPutBook, csvDateList, startDate, endDate, startDateClosePrice, columnIndex, i);
                            if (apiResponse.getCode() != 200) {
                                return apiResponse;
                            }

                            // 获取每支股票的权重与涨跌幅的乘积全部相加,得出加权涨跌幅
                            String formatWeightChange = "";
                            String WeightedNetValue = "";

                            DecimalFormat df = new DecimalFormat("#.#####");

                            short cells = sheet.getRow(1).getLastCellNum(); // 一行有多少个单元格
                            for (int k = 3; k <= sheet.getLastRowNum(); k++) {
                                int flag = 0;
                                double weightChange = 0.0;

                                Row row = sheet.getRow(k);
                                Cell change = null;
                                Cell weight = null;
                                for (int j = 3; j < cells; j++) {
                                    if (null != sheet.getRow(k).getCell(j) && StringUtils.isNotEmpty(sheet.getRow(k).getCell(j).toString())) {
                                        change = sheet.getRow(k).getCell(j); // 涨跌幅
                                        weight = sheet.getRow(1).getCell(j); // 权重

                                        weightChange += Double.parseDouble(weight.toString()) * Double.parseDouble(change.toString()) / 100; // 加权涨跌幅
                                        flag = 1;
                                    }
                                }

                                if (flag == 1) {
                                    formatWeightChange = df.format(weightChange); // 精度保留5位
                                    double doubleWeightChange = Double.parseDouble(formatWeightChange); // 将加权涨跌幅保留5位小数转换成double类型
                                    WeightedNetValue = df.format(initialNetValue * (1 + doubleWeightChange)); // 加权净值保留5位小数
                                }

                                row.createCell(2).setCellValue(formatWeightChange);
                                row.createCell(1).setCellValue(WeightedNetValue);

                                if ((DateUtils.parseDate(endDate).toString()).equals(dataFormatter.formatCellValue(sheet.getRow(k).getCell(0)))) {
                                    break;
                                }
                            }

                            // 判断是否为除汇总页最后一页,如果不是获取当前页的加权净值,为下一页初始净值赋值
                            if (i < sheets - 1) {
                                Sheet sheet2 = outPutBook.getSheetAt(i + 1);
                                sheet2.getRow(0).createCell(2).setCellValue(WeightedNetValue);
                            }
                        }
                    }
                }

                if (csvDateList.isEmpty()) {
                    log.error("sheet" + (i + 1) + "页没有符合股票代码的数据");
                    return new ApiResponse(-1, "sheet" + (i + 1) + "页没有符合股票代码的数据");
                }

                int lastRowNum = 0;
                if (newSheet.getLastRowNum() != -1) {
                    lastRowNum = newSheet.getLastRowNum();
                }
                copyFirstTwoColumns(sheet, newSheet, lastRowNum);

                // 保存工作簿到文件
                try (FileOutputStream fileOut = new FileOutputStream(outPutFilePath)) {
                    outPutBook.write(fileOut);
                }
            } // sheet循环
            return new ApiResponse(200, "导入输出表成功,输出表位置为:" + outPutFilePath);
        } catch (IOException e) {
            log.error("处理 Excel 文件时发生错误:", e.getMessage());
            return new ApiResponse(-1, "处理 Excel 文件时发生错误:" + e.getMessage());
        }
    }

    @Override
    public List<TradeDay> getTradeDate(String startDate, String endDate, String token) {
        List<TradeDay> result = new ArrayList<>();
        HttpURLConnection conn = null;

        try {
            // 1. 创建连接
            URL url = new URL(API_URL);
            conn = (HttpURLConnection) url.openConnection();
            conn.setRequestMethod("POST");
            conn.setRequestProperty("Content-Type", "application/json");
            conn.setDoOutput(true);

            // 2. 准备请求参数
            JSONObject params = new JSONObject();
            params.put("api_name", "trade_cal");
            params.put("token", token);

            JSONObject fields = new JSONObject();
            fields.put("exchange", "");
            fields.put("start_date", startDate);
            fields.put("end_date", endDate);
            params.put("params", fields);
            params.put("fields", "exchange,cal_date,is_open,pretrade_date");

            // 3. 发送请求
            try (OutputStream os = conn.getOutputStream()) {
                os.write(params.toString().getBytes(StandardCharsets.UTF_8));
            }

            // 4. 检查HTTP状态码
            int responseCode = conn.getResponseCode();
            if (responseCode != HttpURLConnection.HTTP_OK) {
                throw new IOException("HTTP请求失败，状态码: " + responseCode);
            }

            // 5. 读取响应
            String response;
            try (BufferedReader reader = new BufferedReader(
                    new InputStreamReader(conn.getInputStream(), StandardCharsets.UTF_8))) {
                response = reader.lines().collect(Collectors.joining());
            }

            // 6. 解析JSON
            JSONObject jsonResponse = new JSONObject(response);
            if (jsonResponse.getInt("code") != 0) {
                log.error("API返回错误: " + jsonResponse.getString("msg"));
                return result;
            }

            // 7. 处理数据
            JSONArray items = jsonResponse.getJSONObject("data").getJSONArray("items");
            for (int i = 0; i < items.length(); i++) {
                JSONArray item = items.getJSONArray(i);
                result.add(new TradeDay(
                        item.getString(0),
                        item.getString(1),
                        item.getInt(2),
                        item.getString(3)
                ));
            }
        } catch (IOException e) {
            log.error("网络请求异常", e);
        } catch (JSONException e) {
            log.error("JSON解析异常", e);
        } finally {
            if (conn != null) {
                conn.disconnect();
            }
        }

        return result;
    }

    @Override
    public ApiResponse dividend(FinaIndicatorRequest finaIndicatorRequest, String token) {

        String fields = "ts_code,end_date,ann_date,div_proc,stk_div,stk_bo_rate,stk_co_rate,cash_div,cash_div_tax,record_date,ex_date,pay_date,div_listdate,imp_ann_date,base_date,base_share";

        String requestBody = String.format(
                "{\"api_name\": \"dividend\", \"token\": \"%s\", \"params\": {\"ts_code\": \"%s\", \"ann_date\": \"%s\", \"record_date\": \"%s\",\"ex_date\":\"%s\",\"imp_ann_date\":\"%s\"},\"fields\": \"%s\"}",
                token, finaIndicatorRequest.getTsCode(), finaIndicatorRequest.getAnnDate(), finaIndicatorRequest.getRecordDate(), finaIndicatorRequest.getExDate(), finaIndicatorRequest.getImpAnnDate(), fields
        );

        try {
            URL url = new URL(API_URL);
            HttpURLConnection httpURLConnection = (HttpURLConnection) url.openConnection();
            httpURLConnection.setRequestMethod("POST");
            httpURLConnection.setRequestProperty("Content-Type", "application/json");
            httpURLConnection.setDoOutput(true);

            httpURLConnection.getOutputStream().write(requestBody.getBytes());

            //读取响应
            BufferedReader reader = new BufferedReader(new InputStreamReader(httpURLConnection.getInputStream()));
            StringBuilder response = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                response.append(line);
            }
            reader.close();

            // 导出 Excel 文件
            try {
                parseAndExportToExcel(response.toString(), "dividend" + finaIndicatorRequest.getTsCode() + ".xlsx", "分红送股数据");
                return new ApiResponse(200, "文件生成成功,文件名为:" + "dividend.xlsx");
            } catch (Exception e) {
                return new ApiResponse(-1, "文件生成失败: " + e.getMessage());
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ApiResponse(-1, "调用 Tushare API 失败: " + e.getMessage());
        }
    }

    @Override
    public void dividend(String token) throws InterruptedException {
        // 1. 获取所有股票代码列表
        ApiResponse stockResponse = getTsCode(token);

        // 检查获取股票代码是否成功
        if (stockResponse.getCode() != 0) {
            log.error(stockResponse.getMessage()); // 如果获取股票代码失败，直接返回错误信息
        }

        // 2. 从响应中提取股票代码列表
        List<String> stockCodes = (List<String>) stockResponse.getData();
        if (stockCodes == null || stockCodes.isEmpty()) {
            log.error("未获取到股票代码数据");
        }

        int callsThisMinute = 0;

        for (int i = 0; i < stockCodes.size(); i++) {
            // 检查是否达到480次限制
            if (callsThisMinute >= 480) {

                Thread.sleep(61000); // 暂停剩余时间


                // 重置计数器和时间戳
                callsThisMinute = 0;
            }

            // 您原有的业务逻辑
            FinaIndicatorRequest finaIndicatorRequest = new FinaIndicatorRequest();
            finaIndicatorRequest.setTsCode(stockCodes.get(i).toString());
            finaIndicatorRequest.setAnnDate("");
            finaIndicatorRequest.setRecordDate("");
            finaIndicatorRequest.setExDate("");
            finaIndicatorRequest.setImpAnnDate("");
            dividend(finaIndicatorRequest, token);

            callsThisMinute++; // 增加调用计数

            // 可选：添加微小延迟防止瞬时高峰
            Thread.sleep(10);
        }
    }

    @Override
    public ApiResponse getTsCode(String token) {
        HttpURLConnection connection = null;
        try {
            // 1. 创建URL连接
            URL url = new URL(API_URL);
            connection = (HttpURLConnection) url.openConnection();

            // 2. 设置请求方法、头部和超时
            connection.setRequestMethod("POST");
            connection.setRequestProperty("Content-Type", "application/json");
            connection.setDoOutput(true);

            // 3. 构建请求体JSON
            JSONObject requestBody = new JSONObject();
            requestBody.put("api_name", "stock_basic");
            requestBody.put("token", token);
            requestBody.put("params", new JSONObject().put("list_status", "")); // 空字符串获取所有状态股票
            requestBody.put("fields", "ts_code"); // 只需要股票代码字段

            // 4. 发送请求数据
            try (OutputStream os = connection.getOutputStream()) {
                byte[] input = requestBody.toString().getBytes("utf-8");
                os.write(input, 0, input.length);
            }

            // 5. 获取响应
            int responseCode = connection.getResponseCode();
            if (responseCode == HttpURLConnection.HTTP_OK) {
                // 读取响应内容
                StringBuilder response = new StringBuilder();
                try (BufferedReader br = new BufferedReader(
                        new InputStreamReader(connection.getInputStream(), "utf-8"))) {
                    String responseLine;
                    while ((responseLine = br.readLine()) != null) {
                        response.append(responseLine.trim());
                    }
                }

                // 6. 解析响应数据并提取股票代码
                JSONObject jsonResponse = new JSONObject(response.toString());
                if (jsonResponse.has("data")) {
                    JSONObject data = jsonResponse.getJSONObject("data");
                    JSONArray items = data.getJSONArray("items");

                    // 7. 创建股票代码列表
                    List<String> stockCodes = new ArrayList<>();
                    for (int i = 0; i < items.length(); i++) {
                        JSONArray stock = items.getJSONArray(i);
                        stockCodes.add(stock.getString(0)); // ts_code 是第一个字段
                    }

                    // 8. 返回成功响应
                    return new ApiResponse(0, "成功", stockCodes);
                } else {
                    return new ApiResponse(-1, "Tushare API返回数据格式异常");
                }
            } else {
                return new ApiResponse(responseCode, "Tushare API请求失败，HTTP状态码: " + responseCode);
            }

        } catch (Exception e) {
            e.printStackTrace();
            return new ApiResponse(-1, "调用Tushare API失败: " + e.getMessage());
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
    }


    /**
     * 读取 CSV 数据
     */
    /**
     * 读取 CSV 数据
     */
    private static Map<String, Double> readCsvData(String csvFilePath) {
        Map<String, Double> csvDateList = new HashMap<>();

        try (BufferedReader br = new BufferedReader(new FileReader(csvFilePath))) {
            String line;

            String title = br.readLine();
            if (title == null) {
                log.error("CSV 文件为空: " + csvFilePath);
                return csvDateList;
            }

            String[] titleSplit = title.split(",");
            int closeIndex = -1;
            for (int i = 0; i < titleSplit.length; i++) {
                if ("close".equals(titleSplit[i])) {
                    closeIndex = i;
                    break;
                }
            }

            if (closeIndex == -1) {
                log.error("CSV 文件缺少 close 列: " + csvFilePath);
                return csvDateList;
            }

            while ((line = br.readLine()) != null) {
                String[] values = line.split(",");
                if (values.length <= closeIndex) {
                    log.error("CSV 文件行数据不完整: " + line);
                    continue;
                }

                String dateStr = values[0].trim();
                String closePriceStr = values[closeIndex].trim();

                LocalDate date = DateUtils.parseDate(dateStr);
                if (date == null) {
                    log.error("无法解析日期: " + dateStr);
                    continue;
                }

                try {
                    double closePrice = Double.parseDouble(closePriceStr);
                    csvDateList.put(DateUtils.formatDate(date), closePrice);
                } catch (NumberFormatException e) {
                    log.error("无效的收盘价格式: " + closePriceStr);
                }
            }
        } catch (IOException e) {
            log.error("读取 CSV 文件时发生错误: " + csvFilePath, e);
        }

        return csvDateList;
    }


    /**
     * 获取起始日或上一个可用交易日的收盘价
     */
    private static Double getStartDateClosePrice(Map<String, Double> csvDateList, String startDate) {
        // 将日期字符串转换为 LocalDate 对象并排序
        List<LocalDate> sortedDates = new ArrayList<>();
        Map<LocalDate, String> dateToOriginalFormatMap = new HashMap<>(); // 存储 LocalDate 到原始格式的映射
        for (String dateStr : csvDateList.keySet()) {
            LocalDate date = DateUtils.parseDate(dateStr);
            if (date != null) {
                sortedDates.add(date);
                dateToOriginalFormatMap.put(date, dateStr); // 保存原始格式
            } else {
                System.err.println("无法解析日期: " + dateStr);
            }
        }
        Collections.sort(sortedDates); // 按日期升序排序

        // 将起始日期转换为 LocalDate
        LocalDate startLocalDate = DateUtils.parseDate(startDate);
        if (startLocalDate == null) {
            System.err.println("无法解析起始日期: " + startDate);
            return null;
        }

        // 查找小于或等于起始日期的最后一个可用交易日
        for (int i = sortedDates.size() - 1; i >= 0; i--) {
            LocalDate currentDate = sortedDates.get(i);
            if (currentDate.isBefore(startLocalDate) || currentDate.isEqual(startLocalDate)) {
                String originalDateStr = dateToOriginalFormatMap.get(currentDate); // 获取原始格式的日期字符串
                return csvDateList.get(originalDateStr); // 返回该日期的收盘价
            }
        }

        // 如果未找到，返回 null
        return null;
    }

    /**
     * 计算起始日到结束日每日的涨跌幅，并写入输出表
     */
    private static ApiResponse calculateAndWritePriceChange(Workbook outPutBook, Map<String, Double> csvDateList,
                                                            String startDate, String endDate, double startDateClosePrice, int columnIndex, int sheetIndex) {
        Sheet sheet = outPutBook.getSheetAt(sheetIndex);
        DataFormatter dataFormatter = new DataFormatter();

        // 将起始日期和结束日期转换为 LocalDate
        LocalDate startLocalDate = DateUtils.parseDate(startDate);
        LocalDate endLocalDate = DateUtils.parseDate(endDate);
        if (startLocalDate == null || endLocalDate == null) {
            System.err.println("无法解析起始日期或结束日期");
            return new ApiResponse(-1, "无法解析起始日期或结束日期");
        }

        // 初始化上一个可用日期的涨跌幅
        double lastAvailablePriceChange = 0.0; // 初始值为 0.0

        // 遍历输出表的每一行
        try {
            for (int i = 3; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    String currentDateStr = dataFormatter.formatCellValue(row.getCell(0)); // 第1列

                    // 将当前日期字符串解析为 LocalDate
                    LocalDate currentLocalDate = DateUtils.parseDate(currentDateStr);
                    if (currentLocalDate == null) {
                        System.err.println("无法解析当前日期: " + currentDateStr);
                        return new ApiResponse(-1, "无法解析当前日期:" + currentDateStr);
                    }

                    // 如果当前日期在起始日和结束日之间
                    if ((currentLocalDate.isAfter(startLocalDate) || currentLocalDate.isEqual(startLocalDate)) &&
                            (currentLocalDate.isBefore(endLocalDate) || currentLocalDate.isEqual(endLocalDate))) {
                        double priceChange;

                        // 检查当前日期是否在 CSV 表中存在
                        if (csvDateList.containsKey(currentDateStr)) {
                            // 如果存在，则正常计算涨跌幅
                            double currentClosePrice = csvDateList.get(currentDateStr);
                            priceChange = (currentClosePrice / startDateClosePrice) - 1;
                            // 更新上一个可用日期的涨跌幅
                            lastAvailablePriceChange = priceChange;
                        } else {
                            // 如果不存在，则沿用上一个可用日期的涨跌幅
                            priceChange = lastAvailablePriceChange;
                        }

                        // 四舍五入保留 5 位小数
                        DecimalFormat df = new DecimalFormat("#.#####");
                        String formattedPriceChange = df.format(priceChange);

                        // 将涨跌幅写入输出表的涨跌幅列
                        row.createCell(columnIndex).setCellValue(formattedPriceChange);
                    }
                }
            }
        } catch (Exception e) {
            return new ApiResponse(-1, "日期解析失败:" + e.getMessage());
        }

        return new ApiResponse(200, "日期解析成功");
    }

    // 获取文件夹下所有 CSV 文件的文件名
    public static List<String> listCsvFiles(String folderPath) {
        try {
            List<String> collect = Files.list(Paths.get(folderPath))
                    .filter(path -> Files.isRegularFile(path) && path.toString().endsWith(".csv"))
                    .map(path -> removeFileExtension(path.getFileName().toString()))
                    .collect(Collectors.toList());
            return collect;
        } catch (IOException e) {
            e.printStackTrace();
            return List.of();
        }
    }

    private static String removeFileExtension(String fileName) {
        int lastDotIndex = fileName.lastIndexOf(".");
        return (lastDotIndex == -1) ? fileName : fileName.substring(0, lastDotIndex);
    }

//    public static void removeEmptyRowsInSecondColumn(Sheet sheet) {
//        // 记录需要删除的行号
//        List<Integer> rowsToDelete = new ArrayList<>();
//
//        // 倒序遍历所有行（从最后一行到第 4 行，索引从 0 开始）
//        for (int n = sheet.getLastRowNum(); n >= 3; n--) {
//            Row row = sheet.getRow(n);
//
//            // 检查行是否存在
//            if (row == null) continue;
//
//            // 获取第二列单元格（索引从 0 开始）
//            Cell cell = row.getCell(1);
//
//            // 判断单元格是否为空（未创建、内容为空或字符串为空）
//            boolean isCellEmpty = (cell == null) || (cell.getCellType() == CellType.BLANK) || StringUtils.isEmpty(cell.toString());
//
//            if (isCellEmpty) {
//                rowsToDelete.add(n); // 记录需要删除的行号
//            }
//        }
//
//        // 批量删除行
//        for (int rowIndex : rowsToDelete) {
//            Row row = sheet.getRow(rowIndex);
//            if (row != null) {
//                sheet.removeRow(row);
//                if (rowIndex < sheet.getLastRowNum()) {
//                    sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1);
//                }
//            }
//        }
//
//        System.out.println("Sheet [" + sheet.getSheetName() + "] 处理完成，删除了 " + rowsToDelete.size() + " 行");
//    }


    //获取多个sheet页的前两列,追加到新的sheet页
    private static void copyFirstTwoColumns(Sheet sourceSheet, Sheet newSheet, int newRowIndex) {
        for (int i = 3; i <= sourceSheet.getLastRowNum(); i++) { // 注意 <=，避免漏掉最后一行
            Row sourceRow = sourceSheet.getRow(i);
            if (sourceRow != null) {
                Cell secondCell = sourceRow.getCell(1);
                if (secondCell != null && StringUtils.isNotEmpty(secondCell.toString())) {
                    // 创建新Sheet中的行
                    Row newRow = newSheet.createRow(newRowIndex++);

                    // 只复制前两列
                    for (int j = 0; j < 2; j++) {
                        Cell sourceCell = sourceRow.getCell(j);
                        if (sourceCell != null) {
                            Cell newCell = newRow.createCell(j);

                            // 复制单元格样式
                            CellStyle newCellStyle = newSheet.getWorkbook().createCellStyle();
                            newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
                            newCell.setCellStyle(newCellStyle);

                            // 复制单元格内容
                            switch (sourceCell.getCellType()) {
                                case STRING:
                                    newCell.setCellValue(sourceCell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    newCell.setCellValue(sourceCell.getNumericCellValue());
                                    break;
                                case BOOLEAN:
                                    newCell.setCellValue(sourceCell.getBooleanCellValue());
                                    break;
                                case FORMULA:
                                    newCell.setCellFormula(sourceCell.getCellFormula());
                                    break;
                                case BLANK:
                                    newCell.setBlank();
                                    break;
                                default:
                                    newCell.setCellValue("");
                            }
                        }
                    }
                }
            }
        }
    }


    /**
     * 解析 JSON 数据并导出到 Excel 文件
     *
     * @param jsonResult 返回的 JSON 数据
     * @param fileName   导出的 Excel 文件名
     */
    public void parseAndExportToExcel(String jsonResult, String fileName, String sheetName) throws Exception {
        try {
            // 使用 Gson 解析 JSON
            JsonObject jsonObject = JsonParser.parseString(jsonResult).getAsJsonObject();
            JsonArray data = jsonObject.getAsJsonObject("data").getAsJsonArray("items");
            JsonArray fieldsArray = jsonObject.getAsJsonObject("data").getAsJsonArray("fields");

            // 创建 Excel 工作簿和工作表
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < fieldsArray.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(fieldsArray.get(i).getAsString());
            }

            // 填充数据行
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i + 1); // 从第二行开始填充数据
                JsonArray rowData = data.get(i).getAsJsonArray();
                for (int j = 0; j < rowData.size(); j++) {
                    Cell cell = row.createCell(j);
                    JsonElement element = rowData.get(j);

                    // 检查是否为 JsonNull
                    if (element.isJsonNull()) {
                        cell.setCellValue(""); // 如果值为 null，设置为空字符串
                    } else {
                        cell.setCellValue(element.getAsString()); // 否则获取字符串值
                    }
                }
            }

            // 检查 Sheet 是否为空
            if (sheet.getPhysicalNumberOfRows() <= 1) { // 只有表头行，没有数据行
                throw new RuntimeException("导出的 Excel 文件为空！");
            }

            // 自动调整列宽
            for (int i = 0; i < fieldsArray.size(); i++) {
                sheet.autoSizeColumn(i);
            }

            // 写入 Excel 文件
            try {
                FileOutputStream fileOut = null;
                if (fileName.contains("dividend")) {
                    fileOut = new FileOutputStream("F:\\IdeaProjects\\THSDemo\\Dividend\\" + fileName); // TODO: 2025/3/27  
                } else if (fileName.contains("FinalIndicator")) {
                    fileOut = new FileOutputStream("F:\\IdeaProjects\\THSDemo\\FinalIndicator\\" + fileName);
                } else if (fileName.contains("DailyBasic")) {
                    fileOut = new FileOutputStream("F:\\IdeaProjects\\THSDemo\\DailyBasic\\" + fileName);
                }

                workbook.write(fileOut);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            // 关闭工作簿
            workbook.close();

            System.out.println("数据已成功导出到 " + fileName);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("导出 Excel 文件失败：" + e.getMessage());
        }
    }

    // 读取 退市CSV 文件
    private List<String[]> readDeListingCSV(String filePath) throws IOException {
        List<String[]> data = new ArrayList<>();
        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
            String line;
            String title = br.readLine();

            if (title == null) {
                log.error("CSV 文件为空: " + filePath);
                return data;
            }

            while ((line = br.readLine()) != null) {
                String[] row = line.split(","); // 假设 CSV 文件以逗号分隔
                data.add(row);
            }
        }
        return data;
    }

    // 按日期正序排序
    private void sortByDate(List<String[]> data) throws ParseException {

        Collections.sort(data, (row1, row2) -> {
            try {
                LocalDate date1 = DateUtils.parseDate(row1[1]); // 假设日期在第一列
                LocalDate date2 = DateUtils.parseDate(row2[1]); // 假设日期在第一列
                return date1.compareTo(date2); // 正序排序
            } catch (Exception e) {
                throw new RuntimeException("日期解析失败", e);
            }
        });
    }

    // 写入 退市CSV 文件
    private void writeDeListingCSV(String filePath, List<String[]> data) throws IOException {
        try (BufferedWriter bw = new BufferedWriter(new FileWriter(filePath))) {
            for (String[] row : data) {
                bw.write(String.join(",", row)); // 将行数据以逗号分隔写入
                bw.newLine();
            }
        }
    }

    /**
     * 处理每年的6月和12月第二个周四
     *
     * @param tradeDays 交易日历列表
     */
    public void processSpecialThursdays(List<TradeDay> tradeDays, String token) {
        // 将交易日历转换为按日期快速查找的Map
        Map<LocalDate, TradeDay> tradeDayMap = tradeDays.stream()
                .collect(Collectors.toMap(
                        td -> LocalDate.parse(td.getDate(), BASIC_FORMATTER),
                        td -> td
                ));

        // 处理2010年到2025年
        for (int year = 2010; year <= 2025; year++) {
            // 处理6月第二个周四
            processMonthThursday(year, 6, tradeDayMap, token);

            // 处理12月第二个周四
            processMonthThursday(year, 12, tradeDayMap, token);
        }
    }

    /**
     * 处理指定年份和月份的第二个周四
     *
     * @param year        年份
     * @param month       月份 (6或12)
     * @param tradeDayMap 交易日历Map
     */
    private void processMonthThursday(int year, int month, Map<LocalDate, TradeDay> tradeDayMap, String token) {
        // 获取该月第二个周四
        LocalDate secondThursday = getSecondThursday(year, month);

        // 查找交易日
        TradeDay tradeDay = findTradeDayForDate(secondThursday, tradeDayMap);

        //构造每日数据查询接口参数
        FinaIndicatorRequest finaIndicatorRequest = new FinaIndicatorRequest();
        finaIndicatorRequest.setTsCode("");
        finaIndicatorRequest.setStartDate("");
        finaIndicatorRequest.setEndDate("");

        if (tradeDay != null && null != tradeDay.getDate()) {
            if (tradeDay.getIsOpen() == 1) {
                // 如果是交易日，执行A方法
                finaIndicatorRequest.setTradeDate(tradeDay.getDate());
                callDailyBasic(finaIndicatorRequest, token);
            } else {
                // 如果不是交易日，找上一个交易日
                TradeDay prevTradeDay = findPreviousTradeDay(secondThursday, tradeDayMap);
                finaIndicatorRequest.setTradeDate(prevTradeDay.getDate());
                if (prevTradeDay != null) {
                    callDailyBasic(finaIndicatorRequest, token);
                }
            }
        }
    }

    /**
     * 获取指定年份和月份的第二个周四
     */
    private static LocalDate getSecondThursday(int year, int month) {
        return LocalDate.of(year, month, 1)
                .with(TemporalAdjusters.dayOfWeekInMonth(2, DayOfWeek.THURSDAY));
    }

    /**
     * 查找指定日期的交易日
     */
    private static TradeDay findTradeDayForDate(LocalDate date, Map<LocalDate, TradeDay> tradeDayMap) {
        return tradeDayMap.get(date);
    }

    /**
     * 查找上一个交易日
     */
    private static TradeDay findPreviousTradeDay(LocalDate date, Map<LocalDate, TradeDay> tradeDayMap) {
        LocalDate checkDate = date.minusDays(1);
        while (checkDate.getYear() >= 2010) { // 防止无限循环
            TradeDay day = tradeDayMap.get(checkDate);
            if (day != null && day.getIsOpen() == 1) {
                return day;
            }
            checkDate = checkDate.minusDays(1);
        }
        return null;
    }
}